CREATE TABLE  "DEPTNT" 
   (    "DEPTNO" NUMBER(2,0),
    "DNAME" VARCHAR2(14), 
    "LOC" VARCHAR2(13), 
     CONSTRAINT DEPTNT PRIMARY KEY(DEPTNO)
   );

CREATE SEQUENCE DEPTNT_id_seq START WITH 1 INCREMENT BY 1;

create or replace trigger DEPTNT_insert
before insert on DEPTNT
for each row
begin
    select DEPTNT_id_seq.nextval into :new.DEPTNO from dual;
end;
/

insert into DEPTNT(DNAME,LOC) values ('RESEARCH','NEW YORK');
insert into DEPTNT(DNAME,LOC) values ('SALES','WROCLAW');
insert into DEPTNT(DNAME,LOC) values ('OPERATIONS','CHERNIVTSI');
insert into DEPTNT(DNAME,LOC) values ('ACCOUNTING','LONDON');
insert into DEPTNT(DNAME,LOC) values ('DEVELOPMENT','SUMY');

CREATE TABLE  "EMPL"
   (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE, 
    "ENAME" VARCHAR2(10), 
    "JOB" VARCHAR2(9), 
    "HIREDATE" DATE, 
    "MGR" NUMBER(4,0),
    "SAL" NUMBER(7,2), 
    "COMM" NUMBER(7,2), 
    "DEPTNO" NUMBER(2,0), 
    CONSTRAINT EMPL PRIMARY KEY(EMPNO)
   );

ALTER TABLE "EMPL" ADD FOREIGN KEY ("MGR") REFERENCES "EMPL" ("EMPNO") ENABLE;

ALTER TABLE "EMPL" ADD FOREIGN KEY ("DEPTNO") REFERENCES  "DEPTNT" ("DEPTNO") ENABLE;

CREATE SEQUENCE EMPL_id_seq START WITH 1 INCREMENT BY 1;

create or replace trigger EMPL_insert
before insert on EMPL
for each row
begin
    select EMPL_id_seq.nextval into :new.EMPNO from dual;
end;
/

insert into EMPL(ENAME,JOB,HIREDATE,MGR,SAL,COMM,DEPTNO) values ('JONES','TEAM LEAD',TO_DATE('2010-02-23','YYYY-MM-DD'),NULL,5000,12,1);
insert into EMPL(ENAME,JOB,HIREDATE,MGR,SAL,COMM,DEPTNO) values ('WARD','DEVELOPER',TO_DATE('2005-10-21','YYYY-MM-DD'),1,4353,4,5);
insert into EMPL(ENAME,JOB,HIREDATE,MGR,SAL,COMM,DEPTNO) values ('BLAKE','ADMIN',TO_DATE('2013-11-24','YYYY-MM-DD'),1,1433,5,5);
insert into EMPL(ENAME,JOB,HIREDATE,MGR,SAL,COMM,DEPTNO) values ('CLARK','DESIGNER',TO_DATE('2012-01-06','YYYY-MM-DD'),1,2733,NULL,3);
insert into EMPL(ENAME,JOB,HIREDATE,MGR,SAL,COMM,DEPTNO) values ('MARTIN','DEVELOPER',TO_DATE('2011-12-12','YYYY-MM-DD'),2,3789,NULL,4);
insert into EMPL(ENAME,JOB,HIREDATE,MGR,SAL,COMM,DEPTNO) values ('FORD','CLERK',TO_DATE('2012-05-16','YYYY-MM-DD'),2,6434,3,1);
insert into EMPL(ENAME,JOB,HIREDATE,MGR,SAL,COMM,DEPTNO) values ('SMITH','DEVELOPER',TO_DATE('2012-02-12','YYYY-MM-DD'),2,3000,NULL,5);
insert into EMPL(ENAME,JOB,HIREDATE,MGR,SAL,COMM,DEPTNO) values ('ADAMS','DESIGNER',TO_DATE('2015-07-26','YYYY-MM-DD'),6,7400,6,3);
insert into EMPL(ENAME,JOB,HIREDATE,MGR,SAL,COMM,DEPTNO) values ('MILLER','ADMIN',TO_DATE('2015-10-09','YYYY-MM-DD'),4,2433,NULL,2);
insert into EMPL(ENAME,JOB,HIREDATE,MGR,SAL,COMM,DEPTNO) values ('SCOTT','DESIGNER',TO_DATE('2009-11-19','YYYY-MM-DD'),6,2321,3,3);
insert into EMPL(ENAME,JOB,HIREDATE,MGR,SAL,COMM,DEPTNO) values ('TURNER','CLERK',TO_DATE('2012-09-20','YYYY-MM-DD'),6,1234,NULL,4);

commit;